﻿using Newtonsoft.Json;

using System;
using System.Collections.Generic;
using System.Data;


namespace Beryl.OrderControllers.DBUpdate
{
    public class reWorkUpdate
    {
        private static reWorkUpdate instance = null;
        private static readonly object uniqueLock = new object();
        public static reWorkUpdate Instance
        {
            get
            {
                lock (uniqueLock)
                {
                    if (instance == null)
                    {
                        instance = new reWorkUpdate();
                    }
                }
                return instance;
            }
        }
        public reWorkUpdate() { }

        //删除需要更新的批次
        public void deleteProLot(Dictionary<string, Dictionary<string, Dictionary<string, string>>> proOrderObj)
        {
            List<string> sqlArr = new List<string>();
            foreach (var proOrderNo in proOrderObj.Keys)
            {
                string sqlString = string.Format(@"DELETE FROM APS_ProLotOrder WHERE cOrderNo = '{0}';", proOrderNo);
                sqlArr.Add(sqlString);
                foreach (var proLotOrderNo in proOrderObj[proOrderNo].Keys)
                {
                    sqlArr.Add(string.Format(@"DELETE FROM APS_ValidRouting WHERE cOrderNo = '{0}';", proLotOrderNo));
                    sqlArr.Add(string.Format(@"DELETE FROM APS_ValidBOM WHERE cOrderNo = '{0}';", proLotOrderNo));
                    sqlArr.Add(string.Format(@"DELETE FROM APS_POLotandSaleORelat WHERE cMOLotNo = '{0}';", proLotOrderNo));
                }
            }
            if (sqlArr.Count > 0)
            {
                bool ok = CommonMethod.Instance.insertSqlList(sqlArr);
                if (!ok)
                {
                    throw new Exception("删除需要更新的批次失败");
                }
            }
        }

        // 更新生产订单，批次订单
        public void updateProOrder(Dictionary<string, Dictionary<string, Dictionary<string, string>>> proOrderObj)
        {
            //日期
            string today = CommonMethod.Instance.getTimes();
            // sql数组
            List<string> sqlArr = new List<string>();
            //sql语句
            string sqlString1 = "";
            string sqlString3 = "";
            string sqlString2 = string.Format(@"INSERT INTO APS_ProLotOrder (cOrderNo,cMOLotNo,cBegProDate,cStatusNo," +
                        "cStaUpdateTime,cStaUpdater,cMatNo,dLotQty,dDeliveryDate,cOFlag," +
                        "cProductDepNo,cCombination,cLotType,cBarCode,cMakeTime,Coefficient,putProQty) SELECT * FROM ( VALUES ");
            string sqlString4 = "";
            var pro_flag = new Dictionary<string, bool>();
            var proLot_flag = new Dictionary<string, bool>();
            foreach (var proOrderNo in proOrderObj.Keys)
            {
                foreach (var proLotOrderNo in proOrderObj[proOrderNo].Keys)
                {
                    // 批次数量
                    string batchNum = proOrderObj[proOrderNo][proLotOrderNo]["batchNum"];
                    // 生产部门编号
                    string cProductDepNo = proOrderObj[proOrderNo][proLotOrderNo]["cProductDepNo"];
                    // 交货期
                    string dDeliveryDate = proOrderObj[proOrderNo][proLotOrderNo]["dDeliveryDate"];

                    if (proOrderObj[proOrderNo][proLotOrderNo].ContainsKey("listJson"))
                    {
                        string json = proOrderObj[proOrderNo][proLotOrderNo]["listJson"];
                        Newtonsoft.Json.Linq.JArray jArray = (Newtonsoft.Json.Linq.JArray)JsonConvert.DeserializeObject(json);

                        for (int i = 0; i < jArray.Count; i++)
                        {
                            string loginUser = jArray[i]["loginUser"].ToString();
                            string Num = jArray[i]["Num"].ToString();
                            string cMatNo = jArray[i]["cMatNo"].ToString();
                            string dPlanQty = jArray[i]["dPlanQty"].ToString();
                            string cSourceNo = jArray[i]["cSourceNo"].ToString();
                            string cPOrderNo = jArray[i]["cPOrderNo"].ToString();
                            string cOFlag = jArray[i]["cOFlag"].ToString();
                            string cCombination = jArray[i]["cCombination"].ToString();
                            string cReworkFlag = jArray[i]["cReworkFlag"].ToString();
                            string cBigBegProDate = jArray[i]["cBigBegProDate"].ToString();
                            string iBatchMult = jArray[i]["iBatchMult"].ToString();
                            string cInOutType = "";
                            string Coefficient = "";

                            if (Coefficient == "")
                            {
                                Coefficient = "1";
                            }
                            float putProQty = float.Parse(Num) * float.Parse(Coefficient);
                            string proLotOrderNoHalf = "";
                            float cProLotFlag = 0;
                            if (pro_flag == null || !pro_flag.ContainsKey(cPOrderNo))
                            {
                                // 更新计划订单状态
                                sqlString1 = string.Format(@"UPDATE APS_PlanOrder SET cStatusNo = '0050',cStaUpdateTime = '{0}' WHERE cPOrderNo = '{1}';", today, cPOrderNo);
                                sqlArr.Add(sqlString1);
                                pro_flag.Add(cPOrderNo, true);
                            }
                            // 更新批次订单表
                            string cBarCodes = "";
                            string cLotType = "";
                            if (proLot_flag == null || !proLot_flag.ContainsKey(proLotOrderNo))
                            {
                                if (cInOutType == "output")
                                {
                                    string cBarCodeHalf = jArray[i]["cBarCodeHalf"].ToString();
                                    cBarCodes = cBarCodeHalf;
                                }
                                else
                                {
                                    string cBarCode = jArray[i]["cBarCode"].ToString();
                                    cBarCodes = cBarCode;
                                }
                                if (proLotOrderNoHalf != "")
                                {
                                    cLotType = "2";
                                }
                                else
                                {
                                    cLotType = "1";
                                }
                                if (float.Parse(cReworkFlag) != 0)
                                {
                                    cLotType = "4";
                                }
                                sqlString4 += string.Format(@"('{0}','{1}','','0050','{2}','{3}','{4}','{5}','{6}'," +
                                    "'0','{7}','{8}','{9}','{10}','{11}','{12}','{13}'),", proOrderNo, proLotOrderNo, today, loginUser, cMatNo, batchNum, dDeliveryDate, cProductDepNo, cCombination, cLotType,
                                    cBarCodes, today, Coefficient, putProQty);
                                proLot_flag.Add(proLotOrderNo, true);
                            }
                            // 更新批次与销售订单关系表
                            sqlString3 = string.Format(@"INSERT INTO APS_POLotandSaleORelat (cMOLotNo,cSourceNo,dQty) VALUES ('{0}','{1}', '{2}');", proLotOrderNo, cSourceNo, Num);
                            sqlArr.Add(sqlString3);
                            sqlArr.Add(string.Format(@"UPDATE APS_SOrder SET cStatusNo = '0050',cStaUpdateTime = '{0}' WHERE cSONo = '{1}' AND cStatusNo = '0020';", today, cSourceNo));
                        }
                    }
                }
            }
            if (sqlString4 != "")
            {
                sqlString4 = sqlString4.Substring(0, sqlString4.Length - 1);
                sqlString2 += sqlString4 + ") A (Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col13,Col14,Col15,Col16,Col17);";
                sqlArr.Add(sqlString2);
            }
            if (sqlArr.Count > 0)
            {
                bool ok = CommonMethod.Instance.insertSqlList(sqlArr);
                if (!ok)
                {
                    throw new Exception("新生产订单，批次订单失败");
                }
            }
        }

        //插入批次物料总需求
        public void insertBatchMatTotalQty(Dictionary<string, Dictionary<string, Dictionary<string, Dictionary<string, string>>>> proLotMatQtyObj)
        {
            List<string> sqlArr = new List<string>();
            string sqlString = "INSERT INTO APS_ValidBOM (cOrderNo,cOrderType,cMatNo,cOperationNo,cOperationName,cOpSeq,cInOutType,cMatandResNo,cMatandResName,cMatandResType,dwidth,dlength,cSpecifution,cUnit,dReqQty,cCapacityLow,cCapacityup,cCapacity,cBomid,cCateNo," +
                "dVoltage,Unit,cBatchNumber,dPlanQty,cSortSeq) SELECT * FROM ( VALUES ";
            string sqlString1 = "";
            float length1 = 0;

            float length2 = 0;
            length2 = proLotMatQtyObj.Count;

            foreach (var cMOLotNo in proLotMatQtyObj.Keys)
            {
                string cOrderNo = cMOLotNo;
                length1++;
                foreach (var cMatNo in proLotMatQtyObj[cMOLotNo].Keys)
                {
                    float length3 = 0;
                    float length4 = proLotMatQtyObj[cMOLotNo][cMatNo].Count;
                    if (length4 == 0)
                    {
                        continue;
                    }
                    foreach (var num in proLotMatQtyObj[cMOLotNo][cMatNo].Keys)
                    {
                        length3++;
                        //string cOrderType = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cOrderType"];
                        //// let ResNo = proLotMatQtyObj[cMOLotNo][cMatNo][num].ResNo;
                        //string ResNo = cMatNo;
                        //string cOperationNo = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cOperationNo"];
                        //string cOperationName = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cOperationName"];
                        //string cOpSeq = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cOpSeq"];
                        //string cInOutType = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cInOutType"];
                        //string tmp_proNo = num;
                        //string cMatandResName = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cMatandResName"];
                        //string cMatandResType = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cMatandResType"];
                        //string dPlanQty = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cMatTotal"];
                        //string dWidth = proLotMatQtyObj[cMOLotNo][cMatNo][num]["dWidth"];
                        //string dLength = proLotMatQtyObj[cMOLotNo][cMatNo][num]["dLength"];
                        //string cSpecifution = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cSpecifution"];
                        //string cUnit = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cUnit"];
                        //string TotalQty = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cMatTotal"];
                        //string cCapacityLow = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cCapacityLow"];
                        //string cCapacityUp = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cCapacityUp"];
                        //string cCapacity = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cCapacity"];
                        //string cBomid = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cBomid"];
                        //string cCateNo = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cCateNo"];
                        //string dVoltage = proLotMatQtyObj[cMOLotNo][cMatNo][num]["dVoltage"];
                        //string Unit = proLotMatQtyObj[cMOLotNo][cMatNo][num]["Unit"];
                        //string cBatchNumber = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cBatchNumber"];
                        //string cSortSeq = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cSortSeq"];


                        string cOrderType = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cOrderType"];
                        // let ResNo = proLotMatQtyObj[cMOLotNo][cMatNo][num].ResNo;
                        string ResNo = cMatNo;
                        string cOperationNo = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cOperationNo"];
                        string cOperationName = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cOperationName"];
                        string cOpSeq = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cOpSeq"];
                        string cInOutType = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cInOutType"];
                        string tmp_proNo = num;
                        string cMatandResName = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cMatandResName"];
                        string cMatandResType = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cMatandResType"];
                        string dPlanQty = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cMatTotal"] == "" ? "NULL" : proLotMatQtyObj[cMOLotNo][cMatNo][num]["cMatTotal"];//
                        string dWidth = proLotMatQtyObj[cMOLotNo][cMatNo][num]["dWidth"] == "" ? "NULL" : proLotMatQtyObj[cMOLotNo][cMatNo][num]["dWidth"];//
                        string dLength = proLotMatQtyObj[cMOLotNo][cMatNo][num]["dLength"] == "" ? "NULL" : proLotMatQtyObj[cMOLotNo][cMatNo][num]["dLength"];//
                        string cSpecifution = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cSpecifution"];
                        string cUnit = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cUnit"];
                        string TotalQty = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cMatTotal"] == "" ? "NULL" : proLotMatQtyObj[cMOLotNo][cMatNo][num]["cMatTotal"];//
                        string cCapacityLow = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cCapacityLow"] == "" ? "NULL" : proLotMatQtyObj[cMOLotNo][cMatNo][num]["cCapacityLow"];//
                        string cCapacityUp = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cCapacityUp"] == "" ? "NULL" : proLotMatQtyObj[cMOLotNo][cMatNo][num]["cCapacityUp"];//
                        string cCapacity = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cCapacity"] == "" ? "NULL" : proLotMatQtyObj[cMOLotNo][cMatNo][num]["cCapacity"];//
                        string cBomid = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cBomid"];
                        string cCateNo = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cCateNo"];
                        string dVoltage = proLotMatQtyObj[cMOLotNo][cMatNo][num]["dVoltage"] == "" ? "NULL" : proLotMatQtyObj[cMOLotNo][cMatNo][num]["dVoltage"];//
                        string Unit = proLotMatQtyObj[cMOLotNo][cMatNo][num]["Unit"];
                        string cBatchNumber = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cBatchNumber"];
                        string cSortSeq = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cSortSeq"];

                        if ((length1 == length2) && (length3 == length4))
                        {
                            sqlString1 += string.Format(@"('{0}','{1}','{2}','{3}','{4}','{5}','{6}'," +
                        "'{7}','{8}','{9}',{10},{11},'{12}','{13}',{14}," +
                        "{15},{16},{17},'{18}','{19}',{20},NULLIF('{21}','null'),'{22}'," +
                        "{23},'{24}'),", cOrderNo, cOrderType, ResNo, cOperationNo, cOperationName, cOpSeq, cInOutType, tmp_proNo, cMatandResName,
                        cMatandResType, dWidth, dLength, cSpecifution, cUnit, TotalQty, cCapacityLow, cCapacityUp, cCapacity, cBomid, cCateNo,
                        dVoltage, Unit, cBatchNumber, dPlanQty, cSortSeq);
                        }
                        else
                        {
                            sqlString1 += string.Format(@"('{0}','{1}','{2}','{3}','{4}','{5}','{6}'," +
                        "'{7}','{8}','{9}',{10},{11},'{12}','{13}',{14}," +
                        "{15},{16},{17},'{18}','{19}',{20},NULLIF('{21}','null'),'{22}'," +
                        "{23},'{24}'),", cOrderNo, cOrderType, ResNo, cOperationNo, cOperationName, cOpSeq, cInOutType, tmp_proNo, cMatandResName,
                        cMatandResType, dWidth, dLength, cSpecifution, cUnit, TotalQty, cCapacityLow, cCapacityUp, cCapacity, cBomid, cCateNo,
                        dVoltage, Unit, cBatchNumber, dPlanQty, cSortSeq);
                        }
                    }
                }
            }
            // 数据库更新
            if (sqlString1 != "")
            {
                sqlString1 = sqlString1.Substring(0, sqlString1.Length - 1);
                sqlString += sqlString1 + ") A (Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12," +
                "Col13, Col14, Col15, Col16, Col17, Col18, Col19, Col20, Col21, Col22, Col23, Col24, Col25);";
                sqlArr.Add(sqlString);
            }
            if (sqlArr.Count > 0)
            {
                bool ok = CommonMethod.Instance.insertSqlList(sqlArr);
                if (!ok)
                {
                    throw new Exception("插入批次物料总需求失败");
                }
            }
        }

        //插入批次工艺路线
        public void insertProRouting(Dictionary<string, Dictionary<string, Dictionary<string, Dictionary<string, string>>>> proRoutingObj)
        {
            //日期
            string today = CommonMethod.Instance.getTime3();
            List<string> sqlArr = new List<string>();
            string sqlString = "INSERT INTO APS_ValidRouting(cOrderNo, cOrderType, cMatNo, cOperationNo, cOperationName, cOpSeq, dTransferTime) SELECT * FROM ( VALUES ";
            string sqlString1 = "";
            float length1 = 0;
            float length2 = proRoutingObj.Count;

            foreach (var cOrderNo in proRoutingObj.Keys)
            {
                length1 = length1 + 1;
                float length3 = 0;
                float length4 = proRoutingObj[cOrderNo].Count;
                foreach (var cMatNo in proRoutingObj[cOrderNo].Keys)
                {
                    length3++;
                    float length5 = 0;
                    float length6 = proRoutingObj[cOrderNo][cMatNo].Count;
                    foreach (var cOperationNo in proRoutingObj[cOrderNo][cMatNo].Keys)
                    {
                        length5++;
                        string cOrderType = proRoutingObj[cOrderNo][cMatNo][cOperationNo]["cOrderType"];
                        string cOperationName = proRoutingObj[cOrderNo][cMatNo][cOperationNo]["cOperationName"];
                        string cOpSeq = proRoutingObj[cOrderNo][cMatNo][cOperationNo]["cOpSeq"];
                        //let dTransferTime = proRoutingObj[cOrderNo][cMatNo][cOperationNo].dTransferTime;
                        float dTransferTime = 0;

                        if ((length1 == length2) && (length3 == length4) && (length5 == length6))
                        {
                            sqlString1 += string.Format(@"('{0}','{1}','{2}','{3}','{4}','{5}',{6})", cOrderNo, cOrderType, cMatNo, cOperationNo, cOperationName, cOpSeq, dTransferTime);
                        }
                        else
                        {
                            sqlString1 += string.Format(@"('{0}','{1}','{2}','{3}','{4}','{5}',{6}),", cOrderNo, cOrderType, cMatNo, cOperationNo, cOperationName, cOpSeq, dTransferTime);
                        }
                    }
                }
            }
            // 数据库更新
            if (sqlString1 != "")
            {
                sqlString += sqlString1 + ") A (Col1,Col2,Col3,Col4,Col5,Col6,Col7);";
                sqlArr.Add(sqlString);
            }
            if (sqlArr.Count > 0)
            {
                bool ok = CommonMethod.Instance.insertSqlList(sqlArr);
                if (!ok)
                {
                    throw new Exception("插入批次工艺路线失败");
                }
            }
        }

        //更新重工单状态
        public void updateRework(Dictionary<string, Dictionary<string, Dictionary<string, List<DataRow>>>> reworkObj)
        {
            List<string> sqlAll = new List<string>();
            //string sqlString = "";
            foreach (var cReworkNo in reworkObj.Keys)
            {
                sqlAll.Add(string.Format(@"update APS_Rework set cReasStatus = '已转生产批次' where cReworkNo = '{0}';", cReworkNo));
            }
            if (sqlAll.Count > 0)
            {
                bool ok = CommonMethod.Instance.insertSqlList(sqlAll);
                if (!ok)
                {
                    throw new Exception("更新生产订单表失败");
                }
            }
        }

        // 更新生产订单表
        public void updatePlanOrder(Dictionary<string, List<Dictionary<string, string>>> PlanOrder)
        {
            List<string> sqlArr = new List<string>();
            string sqlString = "";
            try
            {
                string sqlString1 = "INSERT INTO APS_ValidBOM (cOrderNo, cOrderType, cMatNo, cMatandResNo, dPlanQty, cSpecifution, cBatchNumber,dReqQty) SELECT * FROM(VALUES ";
                // 日期
                string today = CommonMethod.Instance.getTime3();
                foreach(var cPOrderNo in PlanOrder.Keys)
                { 
                    string cReworkNo = PlanOrder[cPOrderNo][0]["cReworkNo"];
                    string cOrderType = PlanOrder[cPOrderNo][0]["cOrderType"];
                    string cMatNo = PlanOrder[cPOrderNo][0]["cMatNo"];
                    string cMatandResNo = PlanOrder[cPOrderNo][0]["cMatandResNo"];
                    string dPlanQty = PlanOrder[cPOrderNo][0]["dPlanQty"];
                    string cSpecifution = PlanOrder[cPOrderNo][0]["cSpecifution"];
                    string cBatchNumber = PlanOrder[cPOrderNo][0]["cBatchNumber"];
                    string dReqQty = PlanOrder[cPOrderNo][0]["dReqQty"];
                    sqlString1 += string.Format(@"('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}'),", cPOrderNo, cOrderType, cMatNo, cMatandResNo, dPlanQty, cSpecifution
                        ,cBatchNumber, dReqQty);
                    sqlArr.Add(string.Format(@"UPDATE APS_Rework SET cReasStatus = '已转计划订单', dReasDate = '{0}' WHERE cReworkNo = '{1}';", today, cReworkNo));
                }
                // 数据库更新
                if (sqlString1 != "")
                {
                    sqlString1 = sqlString1.Substring(0, sqlString1.Length - 1);
                    sqlString += sqlString1 + string.Format(")A (Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8);");
                    sqlArr.Add(sqlString);
                }
                if (sqlArr.Count > 0)
                {
                    bool ok = CommonMethod.Instance.insertSqlList(sqlArr);
                    if (!ok)
                    {
                        throw new Exception("更新生产订单表失败");
                    }
                }
            }
            catch(Exception e)
            {
                throw e;
            }
        }
    }
}